********* 집계 함수 ********* 개요 ==== 집계 함수는 데이터를 분석하여 어떤 결과를 추출하고자 할 때 사용하는 함수이다. 집계 함수는 그룹 별로 그룹핑된 결과를 리턴하며, 그룹핑 대상이 되는 칼럼만 출력한다. 다음은 각 년도의 월별 판매 금액을 저장하고 있는 테이블이다. .. code-block:: sql CREATE TABLE sales_mon_tbl ( yyyy INT, mm INT, sales_sum INT ); INSERT INTO sales_mon_tbl VALUES (2000, 1, 1000), (2000, 2, 770), (2000, 3, 630), (2000, 4, 890), (2000, 5, 500), (2000, 6, 900), (2000, 7, 1300), (2000, 8, 1800), (2000, 9, 2100), (2000, 10, 1300), (2000, 11, 1500), (2000, 12, 1610), (2001, 1, 1010), (2001, 2, 700), (2001, 3, 600), (2001, 4, 900), (2001, 5, 1200), (2001, 6, 1400), (2001, 7, 1700), (2001, 8, 1110), (2001, 9, 970), (2001, 10, 690), (2001, 11, 710), (2001, 12, 880), (2002, 1, 980), (2002, 2, 750), (2002, 3, 730), (2002, 4, 980), (2002, 5, 1110), (2002, 6, 570), (2002, 7, 1630), (2002, 8, 1890), (2002, 9, 2120), (2002, 10, 970), (2002, 11, 420), (2002, 12, 1300); 연도별 총 판매 금액은 다음과 같은 질의로 결과를 구할 수 있다. .. code-block:: sql SELECT yyyy, sum(sales_sum) FROM sales_mon_tbl GROUP BY yyyy; :: yyyy sum(sales_sum) ============================= 2000 14300 2001 11870 2002 13450 **집계 함수(aggregate functions)**\ 는 행들의 그룹에 기반하여 각 그룹 당 하나의 결과를 반환한다. **GROUP BY** 절을 포함하면 각 그룹마다 한 행의 집계 결과를 반환한다. **GROUP BY** 절을 생략하면 전체 행에 대해 한 행의 집계 결과를 반환한다. **HAVING** 절은 **GROUP BY** 절이 있는 질의에 조건을 추가할 때 사용한다. 대부분의 집계 함수에 **DISTINCT**, **UNIQUE** 한정자를 사용할 수 있다. **GROUP BY ... HAVING** 절에 대해서는 :ref:`group-by-clause` 을 참고한다. AVG === .. function:: AVG ( [ DISTINCT | DISTINCTROW | UNIQUE | ALL ] expression ) **AVG** 함수는 모든 행에 대한 연산식 값의 산술 평균을 구한다. 하나의 연산식 *expression* 만 인자로 지정되며, 연산식 앞에 **DISTINCT** 또는 **UNIQUE** 키워드를 포함시키면 연산식 값 중 중복을 제거한 후 평균을 구하고, 키워드가 생략되거나 **ALL** 인 경우에는 모든 값에 대해서 평균을 구한다. :param expression: 수치 값을 반환하는 임의의 연산식을 지정한다. 컬렉션 타입의 데이터를 반환하는 연산식은 지정될 수 없다. :param ALL: 모든 값에 대해 평균을 구하기 위해 사용되며, 기본값이다. :param DISTINCT,DISTINCTROW,UNIQUE: 중복이 제거된 유일한 값에 대해서만 평균을 구하기 위해 사용된다. :rtype: DOUBLE 다음은 *demodb* 에서 한국이 획득한 금메달의 평균 수를 반환하는 예제이다. .. code-block:: sql SELECT AVG (gold) FROM participant WHERE nation_code = 'KOR'; :: avg(gold) ========================== 9.600000000000000e+00 COUNT ===== .. function:: COUNT ( * | [ DISTINCT | DISTINCTROW | UNIQUE | ALL ] expression ) **COUNT** 함수는 질의문이 반환하는 결과 행들의 개수를 반환한다. 별표(*)를 지정하면 조건을 만족하는 모든 행(**NULL** 값을 가지는 행 포함)의 개수를 반환하며, **DISTINCT** 또는 **UNIQUE** 키워드를 연산식 앞에 지정하면 중복을 제거한 후 유일한 값을 가지는 행(**NULL** 값을 가지는 행은 포함하지 않음)의 개수만 반환한다. 따라서, 반환되는 값은 항상 정수이며, **NULL** 은 반환되지 않는다. :param expression: 임의의 연산식이다. :param ALL: 주어진 expression의 모든 행의 개수를 구하기 위해 사용되며, 기본값이다. :param DISTINCT,DISTINCTROW,UNIQUE: 중복이 제거된 유일한 값을 가지는 행의 개수를 구하기 위해 사용된다. :rtype: INT 연산식 *expression* 은 수치형 또는 문자열 타입은 물론, 컬렉션 타입 칼럼과 오브젝트 도메인(사용자 정의 클래스)을 가지는 칼럼도 지정될 수 있다. 다음은 *demodb* 에서 역대 올림픽 중에서 마스코트가 존재했었던 올림픽의 수를 반환하는 예제이다. .. code-block:: sql SELECT COUNT(*) FROM olympic WHERE mascot IS NOT NULL; :: count(*) ============= 9 GROUP_CONCAT ============ .. function:: GROUP_CONCAT( [DISTINCT] expression [ORDER BY {col | unsigned_int} [ASC | DESC]] [SEPARATOR str_val] ) **GROUP_CONCAT** 함수는 그룹에서 **NULL** 이 아닌 값들을 연결하여 결과 문자열을 **VARCHAR** 타입으로 반환한다. 질의 결과 행이 없거나 **NULL** 값만 있으면 **NULL** 을 반환한다. :param expression: 수치 또는 문자열을 반환하는 칼럼 또는 연산식 :param str_val: 구분자로 쓰일 문자열 :param DISTINCT: 결과에서 중복되는 값을 제거한다. :param ORDER\ BY: 결과 값의 순서를 지정한다. :param SEPARATOR: 결과 값 사이에 구분할 구분자를 지정한다. 생략하면 기본값인 쉼표(,)를 구분자로 사용한다. :rtype: STRING 리턴 값의 최대 크기는 시스템 파라미터 **group_concat_max_len** 의 설정을 따른다. 기본값은 **1024** 바이트이며, 최소값은 4바이트, 최대값은 33,554,432바이트이다. 최대값을 초과하면 **NULL** 을 반환한다. 중복되는 값을 제거하려면 **DISTINCT** 절을 사용하면 된다. 그룹 결과의 값 사이에 사용되는 기본 구분자는 쉼표(,)이며, 구분자를 명시적으로 표현하려면 **SEPARATOR** 절과 그 뒤에 구분자로 사용할 문자열을 추가한다. 구분자를 제거하려면 **SEPARATOR** 절 뒤에 빈 문자열(empty string)을 입력한다. 결과 문자열에 문자형 데이터 타입이 아닌 다른 타입이 전달되면, 에러를 반환한다. **GROUP_CONCAT** 함수를 사용하려면 다음의 조건을 만족해야 한다. * 입력 인자로 하나의 표현식(또는 칼럼)만 허용한다. * **ORDER BY** 를 이용한 정렬은 오직 인자로 사용되는 표현식(또는 칼럼)에 의해서만 가능하다. * 구분자로 사용되는 문자열은 문자형 타입만 허용하며, 다른 타입은 허용하지 않는다. .. code-block:: sql SELECT GROUP_CONCAT(s_name) FROM code; :: group_concat(s_name) ====================== 'X,W,M,B,S,G' SELECT GROUP_CONCAT(s_name ORDER BY s_name SEPARATOR ':') FROM code; :: group_concat(s_name order by s_name separator ':') ====================== 'B:G:M:S:W:X' CREATE TABLE t(i int); INSERT INTO t VALUES (4),(2),(3),(6),(1),(5); SELECT GROUP_CONCAT(i*2+1 ORDER BY 1 SEPARATOR '') FROM t; :: group_concat(i*2+1 order by 1 separator '') ====================== '35791113' MAX === .. function:: MAX ( [ DISTINCT | DISTINCTROW | UNIQUE | ALL ] expression ) **MAX** 함수는 모든 행에 대하여 연산식 값 중 최대 값을 구한다. 하나의 연산식 *expression* 만 인자로 지정된다. 문자열을 반환하는 연산식에 대해서는 사전 순서를 기준으로 뒤에 나오는 문자열이 최대 값이 되고, 수치를 반환하는 연산식에 대해서는 크기가 가장 큰 값이 최대 값이다. :param expression: 수치 또는 문자열을 반환하는 하나의 연산식을 지정한다. 컬렉션 타입의 데이터를 반환하는 연산식은 지정할 수 없다. :param ALL: 모든 값에 대해 최대 값을 구하기 위해 사용되며, 기본값이다. :param DISTINCT,DISTINCTROW,UNIQUE: 중복이 제거된 유일한 값에 대해서 최대 값을 구하기 위해 사용된다. :rtype: expression의 타입 다음은 올림픽 대회 중 한국이 획득한 최대 금메달의 수를 반환하는 예제이다. .. code-block:: sql SELECT MAX(gold) FROM participant WHERE nation_code = 'KOR'; :: max(gold) ============= 12 MIN === .. function:: MIN ( [ DISTINCT | DISTINCTROW | UNIQUE | ALL ] expression ) **MIN** 함수는 모든 행에 대하여 연산식 값 중 최소 값을 구한다. 하나의 연산식 *expression* 만 인자로 지정된다. 문자열을 반환하는 연산식에 대해서는 사전 순서를 기준으로 앞에 나오는 문자열이 최소 값이 되고, 수치를 반환하는 연산식에 대해서는 크기가 가장 작은 값이 최소 값이다. :param expression: 수치 또는 문자열을 반환하는 하나의 연산식을 지정한다. 컬렉션 타입의 데이터를 반환하는 연산식은 지정할 수 없다. :param ALL: 모든 값에 대해 최소 값을 구하기 위해 사용되며, 기본값이다. :param DISTINCT,DISTINCTROW,UNIQUE: 중복이 제거된 유일한 값에 대해서 최소 값을 구하기 위해 사용된다. :rtype: expression의 타입 다음은 *demodb* 에서 올림픽 대회 중 한국이 획득한 최소 금메달의 수를 반환하는 예제이다. .. code-block:: sql SELECT MIN(gold) FROM participant WHERE nation_code = 'KOR'; :: min(gold) ============= 7 STDDEV, STDDEV_POP ================== .. function:: STDDEV( [ DISTINCT | DISTINCTROW | UNIQUE | ALL ] expression ) .. function:: STDDEV_POP( [ DISTINCT | DISTINCTROW | UNIQUE | ALL ] expression ) **STDDEV** 함수와 **STDDEV_POP** 함수는 동일하며, 모든 행에 대한 연산식 값들에 대한 표준편차, 즉 모표준 편차를 반환한다. **STDDEV_POP** 함수가 SQL:1999 표준이다. 하나의 연산식 *expression* 만 인자로 지정되며, 연산식 앞에 **DISTINCT** 또는 **UNIQUE** 키워드를 포함시키면 연산식 값 중 중복을 제거한 후, 모표준 편차를 구하고, 키워드가 생략되거나 **ALL** 인 경우에는 모든 값에 대해 모표준 편차를 구한다. :param expression: 수치를 반환하는 하나의 연산식을 지정한다. :param ALL: 모든 값에 대해 표준 편차를 구하기 위해 사용되며, 기본값이다. :param DISTINCT,DISTINCTROW,UNIQUE: 중복이 제거된 유일한 값에 대해서만 표준 편차를 구하기 위해 사용된다. :rtype: DOUBLE 리턴 값은 :func:`VAR_POP` 리턴 값의 제곱근과 같으며 **DOUBLE** 타입이다. 결과 계산에 사용할 행이 없으면 **NULL** 을 반환한다. 다음은 함수에 적용된 공식이다. .. (TODO - equation) .. image:: /images/stddev_pop.jpg .. warning:: CUBRID 2008 R3.1 이하 버전에서 **STDDEV** 함수는 :func:`STDDEV_SAMP` 와 같은 기능을 수행했다. 다음은 전체 과목에 대해 전체 학생의 모표준 편차를 출력하는 예제이다. .. code-block:: sql CREATE TABLE student (name VARCHAR(32), subjects_id INT, score DOUBLE); INSERT INTO student VALUES ('Jane',1, 78), ('Jane',2, 50), ('Jane',3, 60), ('Bruce', 1, 63), ('Bruce', 2, 50), ('Bruce', 3, 80), ('Lee', 1, 85), ('Lee', 2, 88), ('Lee', 3, 93), ('Wane', 1, 32), ('Wane', 2, 42), ('Wane', 3, 99), ('Sara', 1, 17), ('Sara', 2, 55), ('Sara', 3, 43); SELECT STDDEV_POP (score) FROM student; :: stddev_pop(score) ========================== 2.329711474744362e+01 STDDEV_SAMP =========== .. function:: STDDEV_SAMP( [ DISTINCT | DISTINCTROW | UNIQUE | ALL ] expression ) **STDDEV_SAMP** 함수는 표본 표준편차를 구한다. 하나의 연산식 *expression* 만 인자로 지정되며, 연산식 앞에 **DISTINCT** 또는 **UNIQUE** 키워드를 포함시키면 연산식 값 중 중복을 제거한 후, 표본 표준편차를 구하고, 키워드가 생략되거나 **ALL** 인 경우에는 모든 값에 대해 표본 표준편차를 구한다. :param expression: 수치를 반환하는 하나의 연산식을 지정한다. :param ALL: 모든 값에 대해 표준 편차를 구하기 위해 사용되며, 기본값이다. :param DISTINCT,DISTINCTROW,UNIQUE: 중복이 제거된 유일한 값에 대해서만 표준 편차를 구하기 위해 사용된다. :rtype: DOUBLE 리턴 값은 :func:`VAR_SAMP` 리턴 값의 제곱근과 같으며 **DOUBLE** 타입이다. 결과 계산에 사용할 행이 없으면 **NULL** 을 반환한다. 다음은 함수에 적용된 공식이다. .. (TODO - equation) .. image:: /images/stddev_samp.jpg 다음은 전체 과목에 대해 전체 학생의 표본 표준 편차를 출력하는 예제이다. .. code-block:: sql CREATE TABLE student (name VARCHAR(32), subjects_id INT, score DOUBLE); INSERT INTO student VALUES ('Jane',1, 78), ('Jane',2, 50), ('Jane',3, 60), ('Bruce', 1, 63), ('Bruce', 2, 50), ('Bruce', 3, 80), ('Lee', 1, 85), ('Lee', 2, 88), ('Lee', 3, 93), ('Wane', 1, 32), ('Wane', 2, 42), ('Wane', 3, 99), ('Sara', 1, 17), ('Sara', 2, 55), ('Sara', 3, 43); SELECT STDDEV_SAMP (score) FROM student; :: stddev_samp(score) ========================== 2.411480477888654e+01 SUM === .. function:: SUM ( [ DISTINCT | DISTINCTROW | UNIQUE | ALL ] expression ) **SUM** 함수는 모든 행에 대한 연산식 값들의 합계를 반환한다. 하나의 연산식 *expression* 만 인자로 지정되며, 연산식 앞에 **DISTINCT** 또는 **UNIQUE** 키워드를 포함시키면 연산식 값 중 중복을 제거한 후 합계를 구하고, 키워드가 생략되거나 **ALL** 인 경우에는 모든 값에 대해 합계를 구한다. 단일 값 수식을 **SUM** 함수의 입력으로 사용할 수 있다. :param expression: 수치를 반환하는 하나의 연산식을 지정한다. :param ALL: 모든 값에 대해 합계를 구하기 위해 사용되며, 기본으로 지정된다. :param DISTINCT,DISTICNTROW,UNIQUE: 중복이 제거된 유일한 값에 대해서만 합계를 구하기 위해 사용된다. :rtype: expression의 타입 다음은 *demodb* 에서 역대 올림픽에서 획득한 금메달 수의 합계를 기준으로 10위권 국가와 금메달 총 수를 출력하는 예제이다. .. code-block:: sql SELECT nation_code, SUM(gold) FROM participant GROUP BY nation_code ORDER BY SUM(gold) DESC LIMIT 10; :: nation_code sum(gold) =================================== 'USA' 190 'CHN' 97 'RUS' 85 'GER' 79 'URS' 55 'FRA' 53 'AUS' 52 'ITA' 48 'KOR' 48 'EUN' 45 VARIANCE, VAR_POP ================= .. function:: VARIANCE( [ DISTINCT | DISTINCTROW | UNIQUE | ALL ] expression ) .. function:: VAR_POP( [ DISTINCT | DISTINCTROW | UNIQUE | ALL ] expression ) **VARIANCE** 함수와 **VAR_POP** 함수는 동일하며, 모든 행에 대한 연산식 값들에 대한 분산, 즉 모분산을 반환한다. 분모는 모든 행의 개수이다. 하나의 연산식 *expression* 만 인자로 지정되며, 연산식 앞에 **DISTINCT** 또는 **UNIQUE** 키워드를 포함시키면 연산식 값 중 중복을 제거한 후, 모분산을 구하고, 키워드가 생략되거나 **ALL** 인 경우에는 모든 값에 대해 모분산을 구한다. :param expression: 수치를 반환하는 하나의 연산식을 지정한다. :param ALL: 모든 값에 대해 모분산을 구하기 위해 사용되며, 기본값이다. :param DISTINCT,DISTINCTROW,UNIQUE: 중복이 제거된 유일한 값에 대해서만 모분산을 구하기 위해 사용된다. :rtype: DOUBLE 리턴 값은 **DOUBLE** 타입이며, 결과 계산에 사용할 행이 없으면 **NULL** 을 반환한다. 다음은 함수에 적용된 공식이다. .. image:: /images/var_pop.jpg .. note:: CUBRID 2008 R3.1 이하 버전에서 **VARIANCE** 함수는 :func:`VAR_SAMP` 와 같은 기능을 수행했다. 다음은 전체 과목에 대해 전체 학생의 모분산을 출력하는 예제이다. .. code-block:: sql CREATE TABLE student (name VARCHAR(32), subjects_id INT, score DOUBLE); INSERT INTO student VALUES ('Jane',1, 78), ('Jane',2, 50), ('Jane',3, 60), ('Bruce', 1, 63), ('Bruce', 2, 50), ('Bruce', 3, 80), ('Lee', 1, 85), ('Lee', 2, 88), ('Lee', 3, 93), ('Wane', 1, 32), ('Wane', 2, 42), ('Wane', 3, 99), ('Sara', 1, 17), ('Sara', 2, 55), ('Sara', 3, 43); SELECT VAR_POP(score) FROM student; :: var_pop(score) ========================== 5.427555555555550e+02 VAR_SAMP ======== .. function:: VAR_SAMP( [ DISTINCT | DISTINCTROW | UNIQUE | ALL ] expression ) **VAR_SAMP** 함수는 표본 분산을 반환한다. 분모는 모든 행의 개수 - 1이다. 하나의 연산식 *expression* 만 인자로 지정되며, 연산식 앞에 **DISTINCT** 또는 **UNIQUE** 키워드를 포함시키면 연산식 값 중 중복을 제거한 후, 표본 분산을 구하고, 키워드가 생략되거나 **ALL** 인 경우에는 모든 값에 대해 표본 분산을 구한다. :param expression: 수치를 반환하는 하나의 연산식을 지정한다. :param ALL: 모든 값에 대해 표본 분산을 구하기 위해 사용되며, 기본값이다. :param DISTINCT,DISTINCTROW,UNIQUE: 중복이 제거된 유일한 값에 대해서만 표본 분산을 구하기 위해 사용된다. :rtype: DOUBLE 리턴 값은 **DOUBLE** 타입이며, 결과 계산에 사용할 행이 없으면 **NULL** 을 반환한다. 다음은 함수에 적용된 공식이다. .. image:: /images/var_samp.jpg 다음은 전체 과목에 대해 전체 학생의 표본 분산을 출력하는 예제이다. .. code-block:: sql CREATE TABLE student (name VARCHAR(32), subjects_id INT, score DOUBLE); INSERT INTO student VALUES ('Jane',1, 78), ('Jane',2, 50), ('Jane',3, 60), ('Bruce', 1, 63), ('Bruce', 2, 50), ('Bruce', 3, 80), ('Lee', 1, 85), ('Lee', 2, 88), ('Lee', 3, 93), ('Wane', 1, 32), ('Wane', 2, 42), ('Wane', 3, 99), ('Sara', 1, 17), ('Sara', 2, 55), ('Sara', 3, 43); SELECT VAR_SAMP(score) FROM student; :: var_samp(score) ========================== 5.815238095238092e+02